package com.night.sqlserver.service.impl;

import com.night.sqlserver.entity.source.Person;
import com.night.sqlserver.entity.target.People;
import com.night.sqlserver.mapper.source.SourceMapper;
import com.night.sqlserver.mapper.target.TargetMapper;
import com.night.sqlserver.service.DataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.*;

@Service
public class DataServiceImpl implements DataService {

    @Autowired
    private SourceMapper sourceMapper;
    @Autowired
    private TargetMapper targetMapper;

    @Override
    public List<HashMap> findAllSourceTables() {
        return sourceMapper.findAllTable();
    }

    @Override
    public List<HashMap> findColumnByTable(String tableName) {
        Map map = new HashMap();
        map.put("tableName", tableName);
        return sourceMapper.findColumnByTable(map);
    }

    @Override
    public List<HashMap> isTargetTableExist(Map map) {
        return targetMapper.isTargetTableExist(map);
    }

    @Override
    public String generateCreateTableSQL() {
        return null;
    }

    @Override
    public String generateInsertSQL() {
        return null;
    }

    @Override
    public Map transferTable() {
        List<HashMap> tableList = sourceMapper.findAllTable();
        if (tableList.size() > 0) {
            for (HashMap map : tableList) {
                String tableName = map.get("name").toString();//表名称

                //查询表字段信息
                Map sourceMap = new HashMap();
                sourceMap.put("tableName", tableName);
                List<HashMap> columnList = sourceMapper.findColumnByTable(sourceMap);
                if (columnList.size() > 0) {
                    //查询当前表的主键
                    String primaryKey = "";
                    List<HashMap> pkList = sourceMapper.findPrimaryKey(sourceMap);
                    if (pkList.size() > 0) {
                        primaryKey = pkList.get(0).get("COLUMN_NAME").toString();
                    }

                    StringBuilder sb = new StringBuilder();
                    sb.append("CREATE TABLE " + tableName.toUpperCase() + "\n" +
                            "(\n");

                    for (HashMap _map : columnList) {
                        String columnName = _map.get("COLUMN_NAME").toString().toUpperCase();
                        String isNullable = _map.get("IS_NULLABLE").toString();
                        String dataType = _map.get("DATA_TYPE").toString();
                        Object columnMaxLength = _map.get("CHARACTER_MAXIMUM_LENGTH");
                        if (columnMaxLength == null) {
                            columnMaxLength = "4000";
                        }
                        if (columnName.equalsIgnoreCase(primaryKey)) {
                            sb.append("CONSTRAINT  " + tableName + " PRIMARY KEY (" + primaryKey + "),  \n");
                        }
                        if (isNullable.equalsIgnoreCase("YES")) {
                            sb.append(columnName + "  VARCHAR2(" + columnMaxLength + "),  \n");
                        } else {
                            sb.append(columnName + "  VARCHAR2(" + columnMaxLength + ") NOT NULL,");
                        }

                    }

                    sb.setLength(sb.length() - 1);//去掉最后一个逗号
                    sb.append("  \n)");


                    Map sqlMap = new HashMap();
                    sqlMap.put("sql", sb.toString());
                    targetMapper.executeSQL(sqlMap);
                }
            }
        }
        return null;
    }

    @Override
    public Map transferData() {
        Map resultMap = new HashMap();
        List<HashMap> tableList = sourceMapper.findAllTable();
        if (tableList.size() > 0) {
            for (HashMap map : tableList) {
                long beginTime = System.currentTimeMillis();
                String tableName = map.get("name").toString();

                //查询id最大值
                Map sourceMap = new HashMap();
                sourceMap.put("tableName", tableName);
                List<HashMap> maxIdList = sourceMapper.findMaxId(sourceMap);
                if (maxIdList.size() > 0) {
                    int id = Integer.parseInt(maxIdList.get(0).get("id").toString());
                    while (id > 0) {
                        sourceMap.put("end", id);
                        id = id - 100;
                        sourceMap.put("start", id);
                        List<HashMap> sourceList = sourceMapper.select(sourceMap);
                        if (sourceList.size() > 0) {
                            for (HashMap _map : sourceList) {
                                StringBuilder keySQL = new StringBuilder();
                                StringBuilder valueSQL = new StringBuilder();
                                keySQL.append("insert into " + tableName + "\n(");
                                valueSQL.append("values\n(");
                                Iterator iterator = _map.keySet().iterator();
                                while (iterator.hasNext()) {
                                    String key = iterator.next().toString();
                                    Object value = _map.get(key).toString().trim();
                                    keySQL.append(key + ",");
                                    if (value == null || value.equals("")) {
                                        valueSQL.append(null + ",");
                                    } else {
                                        valueSQL.append("'" + value + "',");
                                    }
                                }
                                valueSQL.setLength(valueSQL.length() - 1);
                                valueSQL.append(")");
                                keySQL.setLength(keySQL.length() - 1);
                                keySQL.append(")\n" + valueSQL.toString());
                                System.out.println(keySQL.toString());
                                Map executeMap = new HashMap();
                                executeMap.put("sql", keySQL.toString());
                                targetMapper.executeSQL(executeMap);
                            }
                        }

                    }
                }

                long endTime = System.currentTimeMillis();
                BigDecimal time = new BigDecimal((endTime - beginTime));
                time = time.divide(new BigDecimal("1000"));

                resultMap.put("表" + tableName + "转移数据花费时间为：", time + "秒");

            }
        }
        return resultMap;
    }

    @Override
    public Person selectById(String id) {
        Map map = new HashMap();
        map.put("id", id);
        return sourceMapper.selectById(map);
    }

    @Override
    public int transferById(String id) {
        Map map = new HashMap();
        map.put("id", id);
        Person person = sourceMapper.selectById(map);
        int i = targetMapper.transferByEntity(person);
        return i;
    }

    @Override
    @Transactional
    public Map batchByEntity(String tableName, int start, int end) {
        Map sourceMap = new HashMap();
        sourceMap.put("start", start);
        sourceMap.put("end", end);
        List<Person> personList = sourceMapper.selectList(sourceMap);
        if (personList.size() > 0) {
            for (int i = 0; i < personList.size(); i++) {
                targetMapper.transferByEntity(personList.get(i));
            }
        }
        return sourceMap;
    }

    @Override
    public Map batch() {
        //1.查询所有表
        //2.查询指定表的最大主键
        List<HashMap> tableList = sourceMapper.findAllTable();
        if (tableList.size() > 0) {
            for (HashMap map : tableList) {
                String tableName = map.get("name").toString();
                Map sourceMap = new HashMap();
                sourceMap.put("tableName", tableName);
                List<HashMap> maxIdList = sourceMapper.findMaxId(sourceMap);
                if (maxIdList.size() > 0) {
                    int id = Integer.parseInt(maxIdList.get(0).get("id").toString());
                    while (id > 0) {
                        int start = id - 100;
                        batchByEntity(tableName, start, id);
                        id = start;
                    }
                }

            }
        }

        return null;
    }


    //这个方式只适合插入MySQL数据库
    @Override
    public Map batchByList() {
        long beginTime = System.currentTimeMillis();
        HashMap resultMap = new HashMap();
        String tableName = "cdsgus";
        Map sourceMap = new HashMap();
        sourceMap.put("tableName", tableName);
        List<HashMap> maxIdList = sourceMapper.findMaxId(sourceMap);
        if (maxIdList.size() > 0) {
            int id = 3;// Integer.parseInt(maxIdList.get(0).get("id").toString());
            while (id > 0) {
                sourceMap.put("end", id);
                id = id - 3;
                sourceMap.put("start", id);
                List<Person> personList = sourceMapper.selectList(sourceMap);
                if (personList.size() > 0) {
                    targetMapper.transferByList(personList);
                }
            }

        }

        long endTime = System.currentTimeMillis();
        BigDecimal time = new BigDecimal((endTime - beginTime));
        time = time.divide(new BigDecimal("1000"));

        resultMap.put("表" + tableName + "转移数据花费时间为：", time + "秒");

        return resultMap;
    }
}
